package com.example.springmvc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

import com.example.springmvc.domain.StudentInfo;

@Service("studentDao")
public class StudentDaoImpl implements DaoBase {

	private DataSource dataSource;
	
	@Autowired
	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}
	 
	public List selectAll() throws SQLException {
		
		String sql = "SELECT * FROM STUDENT_INFO";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		List<StudentInfo> results = new ArrayList<StudentInfo>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
		
		for(Map<String, Object> row : rows){
			StudentInfo studentInfo = new StudentInfo();
			studentInfo.setStudent_id((String) row.get("STUDENT_ID"));
			studentInfo.setName((String) row.get("NAME"));
			studentInfo.setPersonal_id((String) row.get("PERSONAL_ID"));
			studentInfo.setSchool_name((String) row.get("SCHOOL_NAME"));
			studentInfo.setMajor((String) row.get("MAJOR"));
			studentInfo.setPhone_num((String) row.get("PHONE_NUM"));
			studentInfo.setEmail((String) row.get("EMAIL"));
			studentInfo.setAddress((String) row.get("ADDRESS"));
			
			results.add(studentInfo);
		}
		
		return results;
	}

	public Object selectById(String id) throws SQLException {
		
		String sql = "SELECT * FROM STUDENT_INFO WHERE STUDENT_ID=?";
		
		final StudentInfo studentInfo = new StudentInfo();
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.query(sql, new Object[]{ id },
				new RowCallbackHandler(){

					public void processRow(ResultSet rs) throws SQLException {
						// Mapping
						studentInfo.setStudent_id(rs.getString("STUDENT_ID"));
						studentInfo.setId(rs.getString("ID"));
						studentInfo.setPasswd(rs.getString("PASSWD"));
						studentInfo.setName(rs.getString("NAME"));
						studentInfo.setPersonal_id(rs.getString("PERSONAL_ID"));
						studentInfo.setSchool_name(rs.getString("SCHOOL_NAME"));
						studentInfo.setMajor(rs.getString("MAJOR"));
						studentInfo.setPhone_num(rs.getString("PHONE_NUM"));
						studentInfo.setEmail(rs.getString("EMAIL"));
						studentInfo.setAddress(rs.getString("ADDRESS"));
						
					}
		
				});
		return (Object)studentInfo;
	}

	public boolean update(Object o) throws SQLException {
		StudentInfo studentInfo = (StudentInfo)o;
		String sql = "UPDATE STUDENT_INFO " +
				"SET ID = ?, PASSWD = ?, NAME = ?, PERSONAL_ID = ?, SCHOOL_NAME = ?, " +
				"MAJOR = ?, PHONE_NUM = ?, EMAIL = ?, ADDRESS = ? " +
				"WHERE STUDENT_ID = ?";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] {studentInfo.getId(),
											   studentInfo.getPasswd(),
											   studentInfo.getName(),
											   studentInfo.getPersonal_id(),
											   studentInfo.getSchool_name(),
											   studentInfo.getMajor(),
											   studentInfo.getPhone_num(),
											   studentInfo.getEmail(),
											   studentInfo.getAddress(),
											   studentInfo.getStudent_id()});
		
		return true;
	}

	public boolean delete(String id) throws SQLException {

		String sql = "DELETE FROM STUDENT_INFO " +
					"WHERE STUDENT_ID = ?";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] { id });
		
		return true;
				
	}

	public boolean insert(Object o) throws SQLException {
		
		StudentInfo studentInfo = (StudentInfo)o;
		String sql = "INSERT INTO STUDENT_INFO(STUDENT_ID, ID, PASSWD, NAME, " +
				"PERSONAL_ID, SCHOOL_NAME, MAJOR, PHONE_NUM, EMAIL, ADDRESS) " +
				"VALUES(EMPLOYEES_SEQ.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] {studentInfo.getId(),
											   studentInfo.getPasswd(),
											   studentInfo.getName(),
											   studentInfo.getPersonal_id(),
											   studentInfo.getSchool_name(),
											   studentInfo.getMajor(),
											   studentInfo.getPhone_num(),
											   studentInfo.getEmail(),
											   studentInfo.getAddress()});
		
		return false;
	}

	public String checkLogIn(String id, String passwd){
		
		String sql = "SELECT * FROM STUDENT_INFO WHERE ID=? AND PASSWD=?";
		
		final StudentInfo studentInfo = new StudentInfo();
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.query(sql, new Object[]{ id, passwd },
				new RowCallbackHandler(){

					public void processRow(ResultSet rs) throws SQLException {
						// Mapping
						studentInfo.setStudent_id(rs.getString("STUDENT_ID"));
						studentInfo.setId(rs.getString("ID"));
						studentInfo.setPasswd(rs.getString("PASSWD"));
						studentInfo.setName(rs.getString("NAME"));
						studentInfo.setPersonal_id(rs.getString("PERSONAL_ID"));
						studentInfo.setSchool_name(rs.getString("SCHOOL_NAME"));
						studentInfo.setMajor(rs.getString("MAJOR"));
						studentInfo.setPhone_num(rs.getString("PHONE_NUM"));
						studentInfo.setEmail(rs.getString("EMAIL"));
						studentInfo.setAddress(rs.getString("ADDRESS"));
						
					}
				});
		
		return studentInfo.getStudent_id();
		
	}
}
